package zy.dao.vip.visit.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.stereotype.Repository;

import zy.dao.BaseDaoImpl;
import zy.dao.vip.visit.VisitDAO;
import zy.entity.sell.ecoupon.T_Sell_Ecoupon_User;
import zy.entity.vip.member.T_Vip_Member;
import zy.entity.vip.visit.T_Vip_Visit;
import zy.util.CommonUtil;
import zy.util.StringUtil;

@Repository
public class VisitDAOImpl extends BaseDaoImpl implements VisitDAO{

	@Override
	public Integer count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object card_code = params.get("card_code");
		Object card_manager_code = params.get("card_manager_code");
		Object card_shop_code = params.get("card_shop_code");
		Object begin_buy_money = params.get("begin_buy_money");
		Object end_buy_money = params.get("end_buy_money");
		String visit = (String)params.get("visit");
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT COUNT(1) ");
		sql.append(" FROM (");
		
		sql.append(" SELECT vm_cardcode,sp_code,vm_name,vm_sex,mt_name,vm_mobile,sp_name,");
		sql.append(" DATE_FORMAT(sh_sysdate,'%Y-%m-%d %T') AS sh_sysdate,");
		sql.append(" vm_points,vm_used_points,sh_sell_money,vm_manager_code,vi_visit_date,vi_manager,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = vm.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM");
		sql.append(" ( ");
		sql.append(" SELECT sh_shop_code,sh_vip_code,sum(sh_sell_money) sh_sell_money,sh_sysdate,sh.companyid FROM t_sell_shop sh ");
		sql.append(" JOIN t_base_shop sp ON sh.sh_shop_code=sp.sp_code AND sh.companyid=sp.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(card_shop_code)){
			sql.append(" AND sh_shop_code = :card_shop_code");
		}
		sql.append(" AND sh_date = :sell_date");
		sql.append(" AND sh_vip_code <> '' ");
		sql.append(" AND sh.companyid = :companyid ");
		sql.append(" GROUP BY sh_vip_code ");
		sql.append(" ) sell ");
		sql.append(" JOIN t_vip_member vm ON sell.sh_vip_code = vm.vm_code AND sell.companyid = vm.companyid ");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code = vm.vm_shop_code AND shop.companyid = vm.companyid ");
		sql.append(" LEFT JOIN t_vip_membertype mt ON mt.mt_code = vm.vm_mt_code AND mt.companyid = vm.companyid ");
		sql.append(" LEFT JOIN (");
		sql.append(" SELECT vi_visit_date,vi_manager,vi_vm_code,visit.companyid");
		sql.append(" FROM");
		sql.append(" (SELECT DATE_FORMAT(vi_visit_date,'%Y-%m-%d') AS vi_visit_date,vi_manager,vi_vm_code,vi.companyid");
		sql.append(" FROM t_vip_visit vi ");
		Date date =new Date();
		SimpleDateFormat sdft=new SimpleDateFormat("yyyy-MM-dd");
		String dateStr=sdft.format(date);
		sql.append(" WHERE vi.companyid = :companyid and vi_visit_date >='"+dateStr+"' and vi_type=3" );
		sql.append(" GROUP BY vi_vm_code  ) AS visit");
		sql.append(" ) AS visitinfo ON vi_vm_code = vm.vm_code AND visitinfo.companyid = vm.companyid");
		
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		/*会员卡经办人*/
		if (StringUtil.isNotEmpty(card_manager_code)){
			sql.append(" AND vm_manager_code=:card_manager_code ");
		}
		if (StringUtil.isNotEmpty(card_code)){
			sql.append(" AND INSTR(sh_vip_code,:card_code)>0 ");
		}
		if (StringUtil.isNotEmpty(begin_buy_money)){
			sql.append(" AND sh_sell_money>=:begin_buy_money ");	
		}
		if (StringUtil.isNotEmpty(end_buy_money)){
			sql.append(" AND sh_sell_money<=:end_buy_money ");	
		}
		sql.append(" AND vm.companyid=:companyid");
		sql.append(" ) AS temp");
		sql.append(" WHERE 1=1 ");
		if("0".equals(visit)){//未回访
			sql.append(" AND (vi_visit_date IS NULL OR vi_visit_date<:sell_date)");
		}
		else if("1".equals(visit)){
			sql.append(" AND vi_visit_date>=:sell_date");
		}
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Member> list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object card_code = params.get("card_code");
		Object card_manager_code = params.get("card_manager_code");
		Object card_shop_code = params.get("card_shop_code");
		Object begin_buy_money = params.get("begin_buy_money");
		Object end_buy_money = params.get("end_buy_money");
		String visit = (String)params.get("visit");
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT vm_id,vm_code,vm_cardcode,sp_code,vm_name,vm_sex,mt_name,vm_mobile,sp_name as shop_name,");
		sql.append(" sh_sysdate,vm_points,vm_used_points,(vm_points-vm_used_points) AS vm_last_points,");
		sql.append(" sh_sell_money,vm_manager_code,vi_visit_date,vi_manager,vm_manager");
		sql.append(" FROM (");
		
		sql.append(" SELECT vm_id,vm_code,vm_cardcode,sp_code,vm_name,vm_sex,mt_name,vm_mobile,sp_name,");
		sql.append(" DATE_FORMAT(sh_sysdate,'%Y-%m-%d %T') AS sh_sysdate,");
		sql.append(" vm_points,vm_used_points,sh_sell_money,vm_manager_code,vi_visit_date,vi_manager,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = vm.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM");
		sql.append(" ( ");
		sql.append(" SELECT sh_shop_code,sh_vip_code,sum(sh_sell_money) sh_sell_money,sh_sysdate,sh.companyid FROM t_sell_shop sh ");
		sql.append(" JOIN t_base_shop sp ON sh.sh_shop_code=sp.sp_code AND sh.companyid=sp.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(card_shop_code)){
			sql.append(" AND sh_shop_code = :card_shop_code");
		}
		sql.append(" AND sh_date = :sell_date");
		sql.append(" AND sh_vip_code <> '' ");
		sql.append(" AND sh.companyid = :companyid ");
		sql.append(" GROUP BY sh_vip_code ");
		sql.append(" ) sell ");
		sql.append(" JOIN t_vip_member vm ON sell.sh_vip_code = vm.vm_code AND sell.companyid = vm.companyid ");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code = vm.vm_shop_code AND shop.companyid = vm.companyid ");
		sql.append(" LEFT JOIN t_vip_membertype mt ON mt.mt_code = vm.vm_mt_code AND mt.companyid = vm.companyid ");
		sql.append(" LEFT JOIN (");
		sql.append(" SELECT vi_visit_date,vi_manager,vi_vm_code,visit.companyid");
		sql.append(" FROM");
		sql.append(" (SELECT DATE_FORMAT(vi_visit_date,'%Y-%m-%d') AS vi_visit_date,vi_manager,vi_vm_code,vi.companyid");
		sql.append(" FROM t_vip_visit vi ");
		Date date =new Date();
		SimpleDateFormat sdft=new SimpleDateFormat("yyyy-MM-dd");
		String dateStr=sdft.format(date);
		sql.append(" WHERE vi.companyid = :companyid and vi_visit_date >='"+dateStr+"' and vi_type=3" );
		sql.append(" GROUP BY vi_vm_code  ) AS visit");
		sql.append(" ) AS visitinfo ON vi_vm_code = vm.vm_code AND visitinfo.companyid = vm.companyid");
		
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		/*会员卡经办人*/
		if (StringUtil.isNotEmpty(card_manager_code)){
			sql.append(" AND vm_manager_code=:card_manager_code ");
		}
		if (StringUtil.isNotEmpty(card_code)){
			sql.append(" AND INSTR(vm_cardcode,:card_code)>0 ");
		}
		if (StringUtil.isNotEmpty(begin_buy_money)){
			sql.append(" AND sh_sell_money>=:begin_buy_money ");	
		}
		if (StringUtil.isNotEmpty(end_buy_money)){
			sql.append(" AND sh_sell_money<=:end_buy_money ");	
		}
		sql.append(" AND vm.companyid=:companyid");
		sql.append(" ) AS temp");
		sql.append(" WHERE 1=1 ");
		if("0".equals(visit)){//未回访
			sql.append(" AND (vi_visit_date IS NULL OR vi_visit_date<:sell_date)");
		}
		else if("1".equals(visit)){
			sql.append(" AND vi_visit_date>=:sell_date");
		}
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY sh_sysdate DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public Map<String, Object> sum(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object card_code = params.get("card_code");
		Object card_manager_code = params.get("card_manager_code");
		Object card_shop_code = params.get("card_shop_code");
		Object begin_buy_money = params.get("begin_buy_money");
		Object end_buy_money = params.get("end_buy_money");
		String visit = (String)params.get("visit");
		
		StringBuffer sql = new StringBuffer("");
		sql.append(" SELECT ");
		sql.append(" IFNULL(SUM(sh_sell_money), 0) AS sh_sell_money,");
		sql.append(" IFNULL(SUM(vm_points), 0) AS vm_points,");
		sql.append(" IFNULL(SUM(vm_points-vm_used_points), 0) AS vm_last_points");
		sql.append(" FROM (");
		sql.append(" SELECT vm_cardcode,sp_code,vm_name,vm_sex,mt_name,vm_mobile,sp_name,");
		sql.append(" DATE_FORMAT(sh_sysdate,'%Y-%m-%d %T') AS sh_sysdate,");
		sql.append(" vm_points,vm_used_points,sh_sell_money,vm_manager_code,vi_visit_date,vi_manager,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = vm.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM");
		sql.append(" ( ");
		sql.append(" SELECT sh_shop_code,sh_vip_code,sum(sh_sell_money) sh_sell_money,sh_sysdate,sh.companyid FROM t_sell_shop sh ");
		sql.append(" JOIN t_base_shop sp ON sh.sh_shop_code=sp.sp_code AND sh.companyid=sp.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(card_shop_code)){
			sql.append(" AND sh_shop_code = :card_shop_code");
		}
		sql.append(" AND sh_date = :sell_date");
		sql.append(" AND sh_vip_code <> '' ");
		sql.append(" AND sh.companyid = :companyid ");
		sql.append(" GROUP BY sh_vip_code ");
		sql.append(" ) sell ");
		sql.append(" JOIN t_vip_member vm ON sell.sh_vip_code = vm.vm_code AND sell.companyid = vm.companyid ");
		sql.append(" JOIN t_base_shop shop ON shop.sp_code = vm.vm_shop_code AND shop.companyid = vm.companyid ");
		sql.append(" LEFT JOIN t_vip_membertype mt ON mt.mt_code = vm.vm_mt_code AND mt.companyid = vm.companyid ");
		sql.append(" LEFT JOIN (");
		sql.append(" SELECT vi_visit_date,vi_manager,vi_vm_code,visit.companyid");
		sql.append(" FROM");
		sql.append(" (SELECT DATE_FORMAT(vi_visit_date,'%Y-%m-%d') AS vi_visit_date,vi_manager,vi_vm_code,vi.companyid");
		sql.append(" FROM t_vip_visit vi ");
		Date date =new Date();
		SimpleDateFormat sdft=new SimpleDateFormat("yyyy-MM-dd");
		String dateStr=sdft.format(date);
		sql.append(" WHERE vi.companyid = :companyid and vi_visit_date >='"+dateStr+"' and vi_type=3" );
		sql.append(" GROUP BY vi_vm_code  ) AS visit");
		sql.append(" ) AS visitinfo ON vi_vm_code = vm.vm_code AND visitinfo.companyid = vm.companyid");
		
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		/*会员卡经办人*/
		if (StringUtil.isNotEmpty(card_manager_code)){
			sql.append(" AND vm_manager_code=:card_manager_code ");
		}
		if (StringUtil.isNotEmpty(card_code)){
			sql.append(" AND INSTR(sh_vip_code,:card_code)>0 ");
		}
		if (StringUtil.isNotEmpty(begin_buy_money)){
			sql.append(" AND sh_sell_money>=:begin_buy_money ");	
		}
		if (StringUtil.isNotEmpty(end_buy_money)){
			sql.append(" AND sh_sell_money<=:end_buy_money ");	
		}
		sql.append(" AND vm.companyid=:companyid");
		sql.append(" ) AS temp");
		sql.append(" WHERE 1=1 ");
		if("0".equals(visit)){//未回访
			sql.append(" AND (vi_visit_date IS NULL OR vi_visit_date<:sell_date)");
		}
		else if("1".equals(visit)){
			sql.append(" AND vi_visit_date>=:sell_date");
		}
		return namedParameterJdbcTemplate.queryForMap(sql.toString(), params);
	}

	@Override
	public void save_ecoupon_user(T_Sell_Ecoupon_User ecouponUser) {
		StringBuffer sql = new StringBuffer();
		sql.append(" INSERT INTO t_sell_ecoupon_user");
		sql.append(" (ecu_ec_number,ecu_code,ecu_sh_number,ecu_date,ecu_name,ecu_tel,ecu_vip_code,ecu_money,ecu_limitmoney,");
		sql.append(" ecu_shop_code,ecu_begindate,ecu_enddate,ecu_state,ecu_use_number,ecu_use_date,ecu_use_type,ecu_check_no,companyid)");
		sql.append(" VALUES");
		sql.append(" (:ecu_ec_number,:ecu_code,:ecu_sh_number,:ecu_date,:ecu_name,:ecu_tel,:ecu_vip_code,:ecu_money,:ecu_limitmoney,");
		sql.append(" :ecu_shop_code,:ecu_begindate,:ecu_enddate,:ecu_state,:ecu_use_number,:ecu_use_date,:ecu_use_type,:ecu_check_no,:companyid)");
		namedParameterJdbcTemplate.update(sql.toString(), new BeanPropertySqlParameterSource(ecouponUser));
	}
	
	@Override
	public List<T_Vip_Member> list_birthday_visit(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Integer crossYear = (Integer)params.get("crossYear");
		Integer crossYearLunar = (Integer)params.get("crossYearLunar");
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vm_id,vm_code,vm_mobile,vm_cardcode,vm_mt_code,vm_shop_code,vm_name,vm_password,vm_sex,");
		sql.append(" vm_birthday_type,vm_birthday,vm_lunar_birth,vm_state,vm_date,vm_enddate,vm_manager_code,t.companyid,");
		sql.append(" vm_points,vm_used_points,vm_init_points,vm_times,vm_total_money,vm_lastbuy_date,vm_lastbuy_money,");
		sql.append(" sp_name AS shop_name,");
		sql.append(" (SELECT mt_name FROM t_vip_membertype mt WHERE mt_code = vm_mt_code AND mt.companyid = t.companyid LIMIT 1) AS mt_name,");
		sql.append(" (SELECT em_name FROM t_base_emp em WHERE em_code = vm_manager_code AND em.companyid = t.companyid LIMIT 1) AS vm_manager");
		sql.append(" FROM t_vip_member t");
		sql.append(" JOIN t_base_shop sp ON vm_shop_code = sp_code AND t.companyid = sp.companyid");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		sql.append(" AND (");
		if(crossYear == 0){
			sql.append(" (vm_birthday_type = 0 AND DATE_FORMAT(vm_birthday,'%m-%d') >= :begindate AND DATE_FORMAT(vm_birthday,'%m-%d') <= :enddate)");
		}else if(crossYear == 1){
			sql.append(" (vm_birthday_type = 0 AND");
			sql.append(" (");
			sql.append(" (DATE_FORMAT(vm_birthday,'%m-%d') >= :begindate AND DATE_FORMAT(vm_birthday,'%m-%d') <= '12-31')");
			sql.append(" OR");
			sql.append(" (DATE_FORMAT(vm_birthday,'%m-%d') >= '01-01' AND DATE_FORMAT(vm_birthday,'%m-%d') <= :enddate)");
			sql.append(" ))");
		}
		sql.append(" OR ");
		if(crossYearLunar == 0){
			sql.append(" (vm_birthday_type = 1 AND DATE_FORMAT(vm_lunar_birth,'%m-%d') >= :beginLunarDate AND DATE_FORMAT(vm_lunar_birth,'%m-%d') <= :endLunarDate)");
		}else if(crossYearLunar == 1){
			sql.append(" (vm_birthday_type = 1 AND");
			sql.append(" (");
			sql.append(" (DATE_FORMAT(vm_lunar_birth,'%m-%d') >= :beginLunarDate AND DATE_FORMAT(vm_lunar_birth,'%m-%d') <= '12-31')");
			sql.append(" OR");
			sql.append(" (DATE_FORMAT(vm_lunar_birth,'%m-%d') >= '01-01' AND DATE_FORMAT(vm_lunar_birth,'%m-%d') <= :endLunarDate)");
			sql.append(" ))");
		}
		sql.append(")");
		
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY vm_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Member.class));
	}

	@Override
	public Integer detail_count(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_mobile = params.get("vm_mobile");
		Object vi_shop_code = params.get("vi_shop_code");
		Object vi_manager_code = params.get("vi_manager_code");
		Object vi_way = params.get("vi_way");
		Object vi_is_arrive = params.get("vi_is_arrive");
		
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT COUNT(1) ");
		sql.append(" FROM t_vip_visit t ");
		sql.append(" JOIN t_vip_member vm ON vm.vm_code=t.vi_vm_code AND vm.companyid=t.companyid ");
		sql.append(" JOIN t_base_shop sp ON t.vi_shop_code=sp.sp_code AND t.companyid=sp.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(vi_shop_code)){
			sql.append(" AND vi_shop_code = :vi_shop_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND vi_visit_date>=:begindate");
		}
		if(StringUtil.isNotEmpty(enddate)){
			sql.append(" AND vi_visit_date<=:enddate");
		}
		if (StringUtil.isNotEmpty(vm_cardcode)){
			sql.append(" AND INSTR(vm_cardcode,:vm_cardcode)>0 ");
		}
		if (StringUtil.isNotEmpty(vm_mobile)){
			sql.append(" AND INSTR(vm_mobile,:vm_mobile)>0 ");
		}
		if (StringUtil.isNotEmpty(vi_manager_code)){
			sql.append(" AND vi_manager_code = :vi_manager_code");
		}
		if (StringUtil.isNotEmpty(vi_way)){
			sql.append(" AND vi_way = :vi_way");
		}
		if (StringUtil.isNotEmpty(vi_is_arrive)){
			sql.append(" AND vi_is_arrive = :vi_is_arrive");
		}
		sql.append(" AND t.companyid=:companyid");
		Integer count = namedParameterJdbcTemplate.queryForObject(sql.toString(), params, Integer.class);
		return count;
	}

	@Override
	public List<T_Vip_Visit> detail_list(Map<String, Object> params) {
		Object shop_type = params.get(CommonUtil.SHOP_TYPE);
		Object sidx = params.get(CommonUtil.SIDX);
		Object sord = params.get(CommonUtil.SORD);
		Object begindate = params.get("begindate");
		Object enddate = params.get("enddate");
		Object vm_cardcode = params.get("vm_cardcode");
		Object vm_mobile = params.get("vm_mobile");
		Object vi_shop_code = params.get("vi_shop_code");
		Object vi_manager_code = params.get("vi_manager_code");
		Object vi_way = params.get("vi_way");
		Object vi_is_arrive = params.get("vi_is_arrive");
		
		StringBuffer sql = new StringBuffer("");
		sql.append("SELECT vi_vm_code,vm_cardcode as vi_vm_cardcode,vm_mobile as vi_vm_mobile,vi_vm_name,vi_visit_date,vi_manager,vi_content,vi_type,vi_way,vi_is_arrive,vi_remark,");
		sql.append("IF(vi_is_arrive=1, vi_date, '') AS vi_next_date,vi_date ");
		sql.append(" FROM t_vip_visit t ");
		sql.append(" JOIN t_vip_member vm ON vm.vm_code=t.vi_vm_code AND vm.companyid=t.companyid ");
		sql.append(" JOIN t_base_shop sp ON t.vi_shop_code=sp.sp_code AND t.companyid=sp.companyid ");
		if(CommonUtil.ONE.equals(shop_type) || CommonUtil.TWO.equals(shop_type)){//总公司、分公司
			sql.append(getShopSQL(shop_type, 0));
			sql.append(" WHERE 1 = 1");
		}else{//自营、加盟、合伙
			sql.append(" WHERE 1 = 1");
			sql.append(" AND sp_code = :shop_code");
		}
		if (StringUtil.isNotEmpty(vi_shop_code)){
			sql.append(" AND vi_shop_code = :vi_shop_code");
		}
		if(StringUtil.isNotEmpty(begindate)){
			sql.append(" AND vi_visit_date>=:begindate");
		}
		if(StringUtil.isNotEmpty(enddate)){
			sql.append(" AND vi_visit_date<=:enddate");
		}
		if (StringUtil.isNotEmpty(vm_cardcode)){
			sql.append(" AND INSTR(vm_cardcode,:vm_cardcode)>0 ");
		}
		if (StringUtil.isNotEmpty(vm_mobile)){
			sql.append(" AND INSTR(vm_mobile,:vm_mobile)>0 ");
		}
		if (StringUtil.isNotEmpty(vi_manager_code)){
			sql.append(" AND vi_manager_code = :vi_manager_code");
		}
		if (StringUtil.isNotEmpty(vi_way)){
			sql.append(" AND vi_way = :vi_way");
		}
		if (StringUtil.isNotEmpty(vi_is_arrive)){
			sql.append(" AND vi_is_arrive = :vi_is_arrive");
		}
		sql.append(" AND t.companyid=:companyid");
		if(sidx != null && !"".equals(sidx)){
			sql.append(" ORDER BY ").append(sidx).append(" ").append(sord);
		}else {
			sql.append(" ORDER BY vi_id DESC");
		}
		sql.append(" LIMIT :start,:end");
		return namedParameterJdbcTemplate.query(sql.toString(), params, new BeanPropertyRowMapper<>(T_Vip_Visit.class));
	}
	
}
